-- Tabla
create table DEMO01.LOG4JDATA
(
  ID NUMBER       PRIMARY KEY,
  APP             VARCHAR2(20),
  FECHA           DATE,
  FECHASTRING     VARCHAR2(20),
  CLASE           VARCHAR2(500),
  PRIORIDAD       VARCHAR2(5),
  MENSAJE         VARCHAR2(500),
  INFLOCALIZACION VARCHAR2(500),
  THEAD           VARCHAR2(200),
  THROWABLE       VARCHAR2(3999)
);
  
alter table DEMO01.LOG4JDATA
  add primary key (ID)
;


-- Secuencia
create sequence DEMO01.SEC_LOG4JDATA
minvalue 1
maxvalue 99999999999
start with 1
increment by 1
cache 20;

-- Trigger
create or replace trigger DEMO01.T_LOG4JDATA_I
  before insert on DEMO01.LOG4JDATA
  for each row
declare

begin

  IF :new.ID is null then

     SELECT SEC_LOG4JDATA.nextval
     into :new.ID
     from dual;

  end if;

end T_LOG4JDATA_I;

-- Procedimiento de Ejemplo

CREATE OR REPLACE PACKAGE DEMO01.PQ_DEMO01 is
 procedure p_obt_all_entidades(    RC_SALIDA            OUT SYS_REFCURSOR,
                                    an_id_key            in demo01.entidad.key%type,
                                    an_id_value          in demo01.entidad.value%type
                                   );
     end;

CREATE OR REPLACE PACKAGE BODY DEMO01.PQ_DEMO01 is

   procedure p_obt_all_entidades(   RC_SALIDA            OUT SYS_REFCURSOR,
                                    an_id_key            in demo01.entidad.key%type,
                                    an_id_value          in demo01.entidad.value%type
                                   )
    is

     begin
       OPEN RC_SALIDA FOR
         select e.*
         from  entidad e
         where e.key = an_id_key  and e.value = an_id_value;
       
       end;


